/*

	Usage : Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS_BY_WAFER_ID 'G1V999.1','G1V999.1', 1, '03/16/2011','1;2;3' 

	@Lot_Flag : When ever Lot in Transit sending flag as Transit and Lot in Next Process sending @Lot_Flag as WIP

*/
ALTER PROC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS_BY_WAFER_ID
(	
	@LOT_NUMBER		VARCHAR(50),
	@FAB_LOT_NUMBER		VARCHAR(50),	
	@QTY_IN			VARCHAR(50),	
	@Date_In		varchar(50),	
	@WAFER_IDS		VARCHAR(150)
)
AS
BEGIN
			DECLARE @WAFERIDS_AS_COMMA VARCHAR(150)
			DECLARE @WAFER_ID		   VARCHAR(5)	
			DECLARE @WAFER_IDS_TABLE   TABLE ( WAFER_ID INT )
			DECLARE @LOTCURSOR CURSOR

			SET @WAFERIDS_AS_COMMA = REPLACE(@WAFER_IDS, ';',',')
			INSERT INTO @WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @WAFERIDS_AS_COMMA  )

			SET @LOTCURSOR = CURSOR FOR SELECT WAFER_ID FROM @WAFER_IDS_TABLE
			OPEN @LOTCURSOR
			FETCH NEXT FROM @LOTCURSOR INTO @WAFER_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
				IF (SELECT COUNT(ShippedLot_ID) FROM Shipped_Lot SL, Lot_Table LT WHERE SL.Lot_ID = LT.Lot_Id AND Status = 'Transit' and Ship_Status = 0 AND Lot_Number = @LOT_NUMBER AND Ship_Date <= CONVERT(DATETIME, @Date_In ) AND WAFER_ID = @WAFER_ID ) > 0	
				BEGIN
					-- Close any Transit Lot With the Lot Number and Ship Date Less than Date In	
					UPDATE Shipped_Lot SET STATUS = 'Close', SHIP_STATUS = 1, Close_Date = CURRENT_TIMESTAMP WHERE ShippedLot_ID IN ( SELECT ShippedLot_ID FROM Shipped_Lot SL, Lot_Table LT WHERE SL.Lot_ID = LT.Lot_Id AND Status = 'Transit' and Ship_Status = 0 AND Lot_Number = @LOT_NUMBER AND Ship_Date <= CONVERT(DATETIME, @Date_In ) AND WAFER_ID = @WAFER_ID )
				END
				IF (SELECT COUNT(ShippedLot_ID) FROM Shipped_Lot SL, Lot_Table LT WHERE SL.Lot_ID = LT.Lot_Id AND Status = 'Transit' and Ship_Status = 0 AND Fab_Lot_Number = @FAB_LOT_NUMBER AND Ship_Date <= CONVERT(DATETIME, @Date_In ) AND WAFER_ID = @WAFER_ID ) > 0	
				BEGIN
					-- Close any Transit Lot With the Fab Lot Number and Ship Date Less than Date In
					UPDATE Shipped_Lot SET STATUS = 'Close', SHIP_STATUS = 1, Close_Date = CURRENT_TIMESTAMP WHERE ShippedLot_ID IN ( SELECT ShippedLot_ID FROM Shipped_Lot SL, Lot_Table LT WHERE SL.Lot_ID = LT.Lot_Id AND Status = 'Transit' and Ship_Status = 0 AND Fab_Lot_Number = @FAB_LOT_NUMBER AND Ship_Date <= CONVERT(DATETIME, @Date_In ) AND WAFER_ID = @WAFER_ID )	
				END
				SET @WAFER_ID = NULL
				FETCH NEXT FROM @LOTCURSOR INTO @WAFER_ID
			END
			CLOSE @LOTCURSOR
			DEALLOCATE @LOTCURSOR	
			DELETE FROM @WAFER_IDS_TABLE
END

